package com.borya.dao.impl;

import java.time.Clock;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang3.StringUtils;
import org.apache.log4j.Logger;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.borya.dao.SourceModelDAO;
import com.borya.framework.annotation.Component;
import com.borya.framework.core.ServerSqlSession;
import com.borya.model.entity.MerchantModelInfo;
import com.borya.model.entity.ModelDetail;
import com.borya.util.DateUtils;
import com.borya.util.StringUtil;

@Component("sourceModelDAOImpl")
public class SourceModelDAOImpl extends BaseDAOImpl implements SourceModelDAO{

	
	private Logger log = Logger.getLogger(getClass());

	@Override
	public MerchantModelInfo getMerchantModel(String dealerId, int type) {
		try (ServerSqlSession session = getSqlDataSource().session();){
			StringBuilder sb = new StringBuilder(128);
			switch (type) {
			case 1:
				sb.append("select dealer_id,dealer_name,createtime,phone_model");
				sb.append(",discount_model,income_transter_model,'','',referral_code");
				sb.append(" from tb_user_referral ");
				sb.append(" where dealer_id=? limit 1");
				break;

			default:
				sb.append("select dealer_id,company_name,createtime,phone_model");
				sb.append(",discount_model,transfer_model,popchannel,popchannel_name,''");
				sb.append(" from tb_merchant ");
				sb.append(" where dealer_id=? limit 1");
				break;
			}
			MerchantModelInfo info = session.executeQuery(sb.toString(), rs->{
				MerchantModelInfo info2 = null;
				if(rs != null && rs.next()){
					info2 = new MerchantModelInfo();
					info2.setDealerId(rs.getString(1));
					info2.setCompanyName(rs.getString(2));
					info2.setCreateTime(DateUtils.format(rs.getLong(3), "yyyy-MM-dd HH:mm:ss"));
					info2.setSourceMod(rs.getString(4));
					info2.setCountMod(rs.getString(5));
					info2.setTransMod(rs.getString(6));
					info2.setPopChan(rs.getString(7));
					info2.setPopChanName(rs.getString(8));
					info2.setReferraCode(rs.getString(9));
				}
				return info2;
			},dealerId);
			if(info == null){
				log.warn("not found merchant info,dealer_id:"+dealerId);
				return null;
			}
			if(StringUtils.isNotBlank(info.getSourceMod())){
				sb.delete(0, sb.length());
				sb.append("select `describe` from tb_channel_model_phone ");
				sb.append(" where dealer_model=? limit 1");
				String phoneModelDesc = session.executeQuery(sb.toString(), rs->{
					return (rs != null && rs.next())?rs.getString(1):null;
				},info.getSourceMod());
				info.setSourceModDesc(phoneModelDesc);
			}
			if(StringUtils.isNotBlank(info.getCountMod())){
				sb.delete(0, sb.length());
				sb.append("select `describe` from tb_channel_model_money ");
				sb.append(" where discount_model=? limit 1");
				String discountModelDesc = session.executeQuery(sb.toString(), rs->{
					return (rs != null && rs.next())?rs.getString(1):null;
				},info.getCountMod());
				info.setCountModDesc(discountModelDesc);
			}
			if(StringUtils.isNotBlank(info.getTransMod())){
				sb.delete(0, sb.length());
				sb.delete(0, sb.length());
				sb.append("select `describe` from tb_model_transfer ");
				sb.append(" where model_code=? limit 1");
				String discountModelDesc = session.executeQuery(sb.toString(), rs->{
					return (rs != null && rs.next())?rs.getString(1):null;
				},info.getTransMod());
				info.setTransModDesc(discountModelDesc);
			}
			return info;
		} catch (Exception e) {
			log.error(e.getMessage(), e);
		}
		return null;
	}

	@Override
	public JSONObject modelList() {
		try (ServerSqlSession session = getSqlDataSource().session();){
			JSONObject data = new JSONObject();
			StringBuilder sb = new StringBuilder(128);
			sb.append("select dealer_model,`describe` from tb_channel_model_phone");
			sb.append(" group by dealer_model");
			JSONArray arr1 = session.executeQuery(sb.toString(), rs-> {
				if(rs != null){
					JSONArray arr = new JSONArray();
					while(rs.next()){
						JSONObject each = new JSONObject();
						each.put("name", rs.getString(1));
						each.put("desc", rs.getString(2));
						arr.add(each);
					}
					return arr;
				}
				return null;
			});
			if(arr1 == null){
				log.warn("获取资源模型列表为null");
				arr1 = new JSONArray();
			}
			
			sb.delete(0, sb.length());
			sb.append("select discount_model,`describe` from tb_channel_model_money");
			sb.append(" group by discount_model");
			JSONArray arr2 = session.executeQuery(sb.toString(), rs-> {
				if(rs != null){
					JSONArray arr = new JSONArray();
					while(rs.next()){
						JSONObject each = new JSONObject();
						each.put("name", rs.getString(1));
						each.put("desc", rs.getString(2));
						arr.add(each);
					}
					return arr;
				}
				return null;
			});
			if(arr2 == null){
				log.warn("获取资源模型列表为null");
				arr2 = new JSONArray();
			}
			
			sb.delete(0, sb.length());
			sb.append("select model_code,`describe` from tb_model_transfer");
			sb.append(" group by model_code");
			JSONArray arr3 = session.executeQuery(sb.toString(), rs-> {
				if(rs != null){
					JSONArray arr = new JSONArray();
					while(rs.next()){
						JSONObject each = new JSONObject();
						each.put("name", rs.getString(1));
						each.put("desc", rs.getString(2));
						arr.add(each);
					}
					return arr;
				}
				return null;
			});
			if(arr3 == null){
				log.warn("获取资源模型列表为null");
				arr3 = new JSONArray();
			}
			data.put("sourceModList", arr1);
			data.put("countModList", arr2);
			data.put("transModList", arr3);
			return data;
		} catch (Exception e) {
			log.error(e.getMessage(), e);
			return null;
		}
	}

	@Override
	public boolean updMerchant(MerchantModelInfo info, int type) {
		ArrayList<String> sqlList = new ArrayList<>();
		final String sourceMod = info.getSourceMod();
		final String countMod = info.getCountMod();
		final String transMod = info.getTransMod();
		final String dealerId = info.getDealerId();
		
		//修改商户模式sql
		StringBuilder sb = new StringBuilder(128);
		sb.append("update tb_merchant set id=id");
		if(!StringUtil.isNULL(sourceMod))
			sb.append(",phone_model='").append(sourceMod).append("'");
		if(!StringUtil.isNULL(countMod))
			sb.append(",discount_model='").append(countMod).append("'");
		if(!StringUtil.isNULL(transMod))
			sb.append(",transfer_model='").append(transMod).append("'");
		sb.append(" where %s='").append(dealerId).append("'");
		String upmerSql=sb.toString();
		sb.delete(0, sb.length());
		
		switch (type) {
		case 1:
			sb.append("update tb_user_referral set id=id");
			if(!StringUtil.isNULL(sourceMod))
				sb.append(",phone_model='").append(sourceMod).append("'");
			if(!StringUtil.isNULL(countMod))
				sb.append(",discount_model='").append(countMod).append("'");
			if(!StringUtil.isNULL(transMod))
				sb.append(",income_transter_model='").append(transMod).append("'");
			sb.append(" where dealer_id='").append(dealerId).append("' limit 1");
			sqlList.add(sb.toString());
			//更新被修改推广商下商户为新的模式值与推广商一致
			sqlList.add(String.format(upmerSql, "popchannel"));
			break;
		case 2:
			String upMerSql = String.format(upmerSql, "dealer_id")+" limit 1";
			sqlList.add(upMerSql);
			break;
		default:
		}
		int result[] = executeBatch(sqlList);
		log.debug("updMerchant sql result:"+Arrays.toString(result));
		return result != null && result.length>0;
	}

	@Override
	public String allModelDetailStr(int detailType) {
		try (ServerSqlSession session = getSqlDataSource().session();){
			StringBuilder sb = new StringBuilder(128);
			sb.append("select resource_model from tb_channel_model_phone");
			sb.append(" where `type`=? ");
			return session.executeQuery(sb.toString(), rs-> {
				if(rs != null){
					StringBuilder str = new StringBuilder(256);
					int i = 0;
					while(rs.next()){
						if(i > 0){
							str.append(",");
						}
						str.append(rs.getString(1));
						i++;
					}
					return str.toString();
				}
				return null;
			},detailType);
		} catch (Exception e) {
			log.error(e.getMessage(), e);
			return null;
		}
	}
	@Override
	public String allModelDetailStr1(String tableName,String coluomName) {
		StringBuilder sb = new StringBuilder(128);
		sb.append("select group_concat(temp.%s SEPARATOR ',')");
		sb.append(" from (select %s from %s group by %s)temp");
		String sqlStr = String.format(sb.toString(), coluomName,coluomName,tableName,coluomName);
		try (ServerSqlSession session = getSqlDataSource().session();) {
			return session.executeQuery(sqlStr, rs-> {
				return (rs != null && rs.next())? rs.getString(1):null;
			});
		} catch (Exception e) {
			log.error(e.getMessage(), e);
			return null;
		}
	}

	@Override
	public boolean addDetail(ModelDetail detail, int modelType) {
		StringBuilder sb = new StringBuilder(128);
		Object[] obj = null;
		switch (modelType) {
		case 1:
			sb.append("insert into tb_channel_model_phone set ");
			sb.append("dealer_model=?,type=?,resource_model=?,`describe`=?");
			sb.append(",create_time=?");
			obj = new Object[]{detail.getName(),detail.getDetailType()
					,detail.getDetailStr(),detail.getDescStr()
					,Clock.systemDefaultZone().millis()};
			break;
		case 2:
			sb.append("insert into tb_channel_model_money set ");
			sb.append("discount_model=?,discount_type=?,money_type=?,face_money=?");
			sb.append(",discount=?,income_discount=?,`describe`=?,city_code=?,package_pre_code=?");
			sb.append(",create_time=?");
			obj = new Object[]{detail.getName(),detail.getCountType(),detail.getDetailType()
					,detail.getFaceMoney(),detail.getDiscount(),detail.getIncomeDiscount()
					,detail.getDescStr(),detail.getCityCode(),detail.getPackPreCode()
					,Clock.systemDefaultZone().millis()};
			break;
		default:
			sb.append("insert into tb_model_transfer set ");
			sb.append("model_code=?,income_transfer=?,income_alifications=?");
			sb.append(",`describe`=?,create_time=?");
			obj = new Object[]{detail.getName(),detail.getTrans(),detail.getAlification()
					,detail.getDescStr(),Clock.systemDefaultZone().millis()};
			break;
		}
		return executeUpdate(sb.toString(), obj);
	}

	@Override
	public boolean updDetail(ModelDetail detail, int modelType) {
		StringBuilder sb = new StringBuilder(128);
		Object[] obj = null;
		switch (modelType) {
		case 1:
			sb.append("update tb_channel_model_phone set ");
			sb.append("resource_model=?,`describe`=?");
			sb.append(" where id=?");
			obj = new Object[]{detail.getDetailStr(),detail.getDescStr()
					,detail.getOrder()};
			break;
		case 2:
			sb.append("update tb_channel_model_money set ");
			sb.append("discount_type=?,face_money=?");
			sb.append(",discount=?,income_discount=?,`describe`=?,city_code=?,package_pre_code=?");
			sb.append("  where id=?");
			
			obj = new Object[]{detail.getCountType(),detail.getFaceMoney(),detail.getDiscount()
					,detail.getIncomeDiscount(),detail.getDescStr(),detail.getCityCode()
					,detail.getPackPreCode(),detail.getOrder()};
			break;
		default:
			sb.append("update tb_model_transfer set ");
			sb.append("income_transfer=?,income_alifications=?,`describe`=?");
			sb.append("  where id=?");
			
			obj = new Object[]{detail.getTrans(),detail.getAlification()
					,detail.getDescStr(),detail.getOrder()};
			break;
		}
		return executeUpdate(sb.toString(), obj);
	}

	@Override
	public ModelDetail getTransDetail(int modelId) {
		try (ServerSqlSession session = getSqlDataSource().session();){
			StringBuilder sb = new StringBuilder(128);
			sb.append("select id,model_code,income_transfer,income_alifications,`describe`");
			sb.append(" from tb_model_transfer ");
			sb.append(" where id=? limit 1");
			return session.executeQuery(sb.toString(), rs->{
				ModelDetail detail = null;
				if(rs != null && rs.next()){
					detail = new ModelDetail();
					detail.setOrder(rs.getInt(1));
					detail.setName(rs.getString(2));
					detail.setTrans(rs.getInt(3));
					detail.setAlification(rs.getInt(4));
					detail.setDescStr(rs.getString(5));
				}
				return detail;
			},modelId);
		} catch (Exception e) {
			log.error(e.getMessage(), e);
		}
		return null;
	}

	
	@Override
	public ModelDetail getDiscountDetails(int modelId) {
		try (ServerSqlSession session = getSqlDataSource().session();){
			StringBuilder sb = new StringBuilder(128);
			sb.append("select id,discount_model,discount_type,money_type,face_money");
			sb.append(",discount,income_discount,`describe`,city_code,package_pre_code");
			sb.append(",IF(city_code=0,'',(select cityName from tb_citycode where cityCode=tb_channel_model_money.city_code limit 1))");
//			sb.append(",IF(package_pre_code=0,'',(select pack_pre_store from tb_package_prelist where code=tb_channel_model_money.package_pre_code limit 1))");
			sb.append(" from tb_channel_model_money ");
			sb.append(" where id=? ");
			return session.executeQuery(sb.toString(), rs->{
				if(rs != null && rs.next()){
					ModelDetail detail = new ModelDetail();
					detail.setOrder(rs.getInt(1));
					detail.setName(rs.getString(2));
					detail.setCountType(rs.getInt(3));
					detail.setDetailType(rs.getInt(4));
					detail.setFaceMoney(rs.getInt(5));
					detail.setDiscount(rs.getInt(6));
					detail.setIncomeDiscount(rs.getInt(7));
					detail.setDescStr(rs.getString(8));
					detail.setCityCode(rs.getInt(9));
					detail.setPackPreCode(rs.getInt(10));
					detail.setCityName(rs.getString(11));
					detail.setPackPre(rs.getInt(5));
					return detail;
				}
				return null;
			},modelId);
		} catch (Exception e) {
			log.error(e.getMessage(), e);
		}
		return null;
	}
	
	@Override
	public ModelDetail getSourceDetails(int modelId) {
		try (ServerSqlSession session = getSqlDataSource().session();){
			StringBuilder sb = new StringBuilder(128);
			sb.append("select id,dealer_model,type,resource_model,`describe`");
			sb.append(" from tb_channel_model_phone ");
			sb.append(" where id=? ");
			return session.executeQuery(sb.toString(), rs->{
				if(rs != null && rs.next()){
					ModelDetail detail = new ModelDetail();
					detail.setOrder(rs.getInt(1));
					detail.setName(rs.getString(2));
					detail.setDetailType(rs.getInt(3));
					detail.setDetailStr(rs.getString(4));
					detail.setDescStr(rs.getString(5));
					return detail;
				}
				return null;
			},modelId);
		} catch (Exception e) {
			log.error(e.getMessage(), e);
		}
		return null;
	}

	@Override
	public List<ModelDetail> getModelDetailList(int modelType) {
		try (ServerSqlSession session = getSqlDataSource().session();){
			StringBuilder sb = new StringBuilder(128);
			switch (modelType) {
			case 1:
				sb.append("select id,dealer_model,create_time,type,resource_model,`describe`");
				sb.append(" from tb_channel_model_phone order by dealer_model asc,create_time desc");
				return session.executeQuery(sb.toString(), rs->{
					List<ModelDetail> list = null;
					if(rs != null){
						list = new ArrayList<>();
						while(rs.next()){
							ModelDetail detail = new ModelDetail();
							detail.setOrder(rs.getInt(1));
							detail.setName(rs.getString(2));
							detail.setCreateTimeStr(DateUtils.format(rs.getLong(3), "yyyy-MM-dd HH:mm:ss"));
							detail.setDetailType(rs.getInt(4));
							detail.setDetailStr(rs.getString(5).replaceAll(",", "、"));
							detail.setDescStr(rs.getString(6));
							list.add(detail);
						}
					}
					return list;
				});
			case 2:
				sb.append("select id,discount_model,create_time,discount_type,money_type,face_money");
				sb.append(",discount,income_discount");
				sb.append(",IF(city_code=0,'',(select cityName from tb_citycode where cityCode=tb_channel_model_money.city_code limit 1))");
				sb.append(",IF(package_pre_code=0,'',(select package_code from tb_package_prelist where code=tb_channel_model_money.package_pre_code limit 1))");
				sb.append(",`describe`");
				sb.append(",IF(package_pre_code=0,'',(select pack_pre_store from tb_package_prelist where code=tb_channel_model_money.package_pre_code limit 1))");
				sb.append(" from tb_channel_model_money ");
				sb.append(" order by discount_model asc,create_time desc");
				return session.executeQuery(sb.toString(), rs->{
					List<ModelDetail> list = null;
					if(rs != null){
						list = new ArrayList<>();
						while(rs.next()){
							ModelDetail detail = new ModelDetail();
							detail.setOrder(rs.getInt(1));
							detail.setName(rs.getString(2));
							detail.setCreateTimeStr(DateUtils.format(rs.getLong(3), "yyyy-MM-dd HH:mm:ss"));
							detail.setCountType(rs.getInt(4));
							detail.setDetailType(rs.getInt(5));
							detail.setFaceMoney(rs.getInt(6));
							detail.setDiscount(rs.getInt(7));
							detail.setIncomeDiscount(rs.getInt(8));
							detail.setCityName(rs.getString(9));
							detail.setPackCode(rs.getString(10));
							detail.setDescStr(rs.getString(11));
							detail.setPackPre(rs.getLong(12));
							list.add(detail);
						}
					}
					return list;
				});
			default:
				sb.append("select id,model_code,create_time,income_transfer,income_alifications,`describe`");
				sb.append(" from tb_model_transfer order by create_time desc");
				return session.executeQuery(sb.toString(), rs->{
					List<ModelDetail> list = null;
					if(rs != null){
						list = new ArrayList<>();
						while(rs.next()){
							ModelDetail detail = new ModelDetail();
							detail.setOrder(rs.getInt(1));
							detail.setName(rs.getString(2));
							detail.setCreateTimeStr(DateUtils.format(rs.getLong(3), "yyyy-MM-dd HH:mm:ss"));
							detail.setTrans(rs.getInt(4));
							detail.setAlification(rs.getInt(5));
							detail.setDescStr(rs.getString(6));
							list.add(detail);
						}
					}
					return list;
				});
			}
			
		} catch (Exception e) {
			log.error(e.getMessage(), e);
		}
		return null;
	}
	
	@Override
	public Map<String, String> getPackTitles() {
		try (ServerSqlSession session = getSqlDataSource().session();){
			StringBuilder sb = new StringBuilder(128);
			sb.append("select code,title from tb_package group by code");
			return session.executeQuery(sb.toString(), rs->{
				Map<String, String> map = null;
				if(rs != null){
					map = new HashMap<>();
					while(rs.next()){
						map.put(rs.getString(1), rs.getString(2));
					}
				}
				return map;
			});
		} catch (Exception e) {
			log.error(e.getMessage(), e);
			return null;
		}
	}
	
	@Override
	public JSONObject getCityList() {
		String sql = "select cityName,cityCode from tb_citycode where first_char !='' order by first_char asc";
		try (ServerSqlSession session = getSqlDataSource().session();){
			return session.executeQuery(sql, rs-> {
				if(rs!=null){
					JSONObject result = new JSONObject();
					JSONArray rech = new JSONArray();
					JSONArray rech_hot = new JSONArray();
					while(rs.next()){
						JSONObject rechone = new JSONObject();
						JSONObject rechone_hot = new JSONObject();
						String cityName = rs.getString("cityName");
						if(cityName.contains("北京")
								||cityName.contains("上海")
								||cityName.contains("广州")
								||cityName.contains("深圳")){
							rechone_hot.put("cityName",cityName);
							rechone_hot.put("cityCode",rs.getString("cityCode"));
							rech_hot.add(rechone_hot);
						}
						rechone.put("cityName",cityName);
						rechone.put("cityCode",rs.getString("cityCode"));
						
						rech.add(rechone);
					}
					result.put("city", rech);
					result.put("hotCity", rech_hot);
					return result;
				}
				log.error("db error");
				return null;
			});
		} catch (Exception e) {
			log.error(e.getMessage(), e);
			return null;
		}
	}

	@Override
	public JSONArray getPackPreList() {
		try (ServerSqlSession session = getSqlDataSource().session();){
			StringBuilder sb = new StringBuilder(128);
			sb.append("select a.pack_pre_store,a.code");
			sb.append(",(select title from tb_package where code=a.package_code limit 1)");
			sb.append(" from tb_package_prelist a ");
			
			return session.executeQuery(sb.toString(), rs->{
				JSONArray arr = null;
				if(rs != null){
					arr = new JSONArray();
					while(rs.next()){
						JSONObject each = new JSONObject();
						each.put("packPreCode", rs.getString(2));
						each.put("packPreDesc", rs.getString(3)+"/"+rs.getString(1));
						arr.add(each);
					}
				}
				return arr;
			});
		} catch (Exception e) {
			log.error(e.getMessage(), e);
			return null;
		}
	}

	public static void main(String[] args) {
		StringBuilder sb = new StringBuilder();
		sb.append("select group_concat(temp.%s SEPARATOR ',')");
		sb.append(" from (select %s from %s group by %s)temp");
		String format = String.format(sb.toString(), "colume","colume","table","colume");
		System.out.println(format);
	}
	
}
